/*  
 exec SP_BOM_INVENTROY_REPORT_proc '0','ASSEMBLY','0'
 exec SP_BOM_INVENTROY_REPORT_proc '0','FINISHED GOODS','0'
  THIS PROCEDURE LOADS ALL THE BOM LOTS IN CHIPTRAC.
select * from product_map_table where processstep_id = 1575
select * from product where p_type = 'bom'
 */
Alter PROC SP_BOM_INVENTROY_REPORT_PROC

@PROCESS_STEP_ID VARCHAR(50),
@PROCESS_TYPE	VARCHAR(50),
@DEFAULT_PROMPT VARCHAR(20)
  
AS
BEGIN
	DECLARE @SQLSTRING NVARCHAR(4000)

	SET @SQLSTRING = N'
	SELECT 
		 LT.PROCESSSTEP_ID,LOT_NUMBER,WAFER_QTY_IN,QTY_IN,DATE_IN,
		 (CASE WHEN LOT_CURRENT_STATUS = ''RUNNING'' THEN ''ACTIVE''
		 ELSE LOT_CURRENT_STATUS
		 END) AS LOT_CURRENT_STATUS,PART_NUMBER,
		 (SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = P.PRODUCT_ID) AS BASE_PRODUCT,
		 PS_TYPE_NAME,PS_TYPE,
		 (CASE
			WHEN PS_TYPE = ''FAB'' THEN 1
			WHEN PS_TYPE = ''WAFER BANK'' THEN 2
			WHEN PS_TYPE = ''SORT'' THEN 3
			WHEN PS_TYPE = ''Die Bank'' THEN 4
			WHEN PS_TYPE = ''ASSEMBLY'' THEN 5
			WHEN PS_TYPE = ''FT'' THEN 6
			WHEN PS_TYPE = ''QA''  THEN  7
			WHEN PS_TYPE = ''FG'' THEN 8
		   END 
		) AS PS_TYPE_ID,
		 L_VENDOR_NAME,
		 (SELECT TOP 1 PART_NUMBER FROM PRODUCT_MAP_TABLE WHERE PROCESSSTEP_ID = LT.PROCESSSTEP_ID /*AND LOCATION_ID = LT.LOCATION_ID*/) AS EQUIV_PART_NUMBER
	FROM 
		LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS,LOCATION L
	WHERE
		LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND LT.LOCATION_ID = L.LOCATION_ID
		AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' )
		AND P_TYPE = ''BOM'' '

	IF @PROCESS_STEP_ID IS NOT NULL AND @PROCESS_STEP_ID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.PROCESSSTEP_ID = '''+ @PROCESS_STEP_ID+''''
	END
	
	IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND PS_TYPE_NAME = '''+ @PROCESS_TYPE+''''
	END

	--PRINT '@SQLSTRING = '+@SQLSTRING
	EXEC(@SQLSTRING)
END